<?php
namespace app\admin\controller;

use think\Request;
use think\Db;
use app\admin\model\DataTable as DataTableModel;
use app\admin\model\TableExtraColumn as TableExtraColumnModel;

/**
 * @data   2017-11-15 15:22:57
 * @author huwhis@163.com
 * @version   0.0.1
 */
class DataTable extends Base
{
    protected $model;

    public function __construct(Request $request = null)
    {
        parent::__construct($request);

        $this->model = new DataTableModel();
    }

    public function index($page = 0)
    {
        $data = $this->model->where('is_del', 0)->field('id, table_name_cn, table_name_en, create_time')->order('id', 'desc')->paginate();
        
        $this->assign("data", $data);

        return $this->fetch();
    }

    public function addOrEdit($id = 0)
    {
        if ($id != 0) {
            $data = $this->model->find($id);
        } else {
            $data = [
                'id' => 0,
                'table_name_cn' => '',
                'table_name_en' => '',
                'note' => ''
            ];
        }
        
        $this->assign('data', $data);
        return $this->fetch();
    }

    public function doAddOrEdit(Request $request = null, $id = 0)
    {
        if ($request->isPost()) {
            $param = $request->param();

            if ($param['table_name_cn'] == '' || $param['table_name_en'] == '') {
                $this->error("表名(中英)不能为空");
            }

            try {
                if ($param['id'] != 0) {
                    $this->model->save([
                        'table_name_cn' => $param['table_name_cn'],
                        'table_name_en' => $param['table_name_en'],
                        'note' => $param['note'],
                        'modify_time' => time()
                    ], ['id' => $param['id']]);
                } else {
                    $this->model->save([
                        'table_name_cn' => $param['table_name_cn'],
                        'table_name_en' => $param['table_name_en'],
                        'note' => $param['note'],
                        'create_time' => time()
                    ]);
                }
            } catch (\Exception $e) {
                $msg = $e->getMessage();

                $this->error("错误提示:".$msg);
            }
            $this->success('操作成功', 'index');
        }
    }

    /**
     * 逻辑删除
     */
    public function delete($id = null)
    {
        if (Request::instance()->isAjax()) {
            // if ($this->model->where('pid', 'in', $id)->value('id')) {
            //     return ['code'=>0, 'msg'=>'子栏目不为空, 若要删除请先清空子栏目'];
            // }

            if ($this->model->save(['is_del'=>1],['id'=>$id])) {
                return ['code' => 1,'msg'=>"删除成功"];
            } else {
                return ['code' => 0,'msg'=>"删除失败"];
            }
        }
    }

    /**
     * 查看/修改表字段 tableColumn
     */
    public function tableColumn(Request $request = null, $id = 0)
    {
        $table_extra_column_model = new TableExtraColumnModel();
        if ($request->isPost()) {
            echo "test... 功能暂未开放<br/>";
            echo '<button type="button" onclick="window.history.back();" style="margin:0px 20px;">退&nbsp;&nbsp;出</button>';
        } else {
            $table_extra_columns = $table_extra_column_model->where('table_id', $id)
            ->field('id,table_id,column_name_en,column_name_cn,type_length,is_show')
            ->select();
            // echo json_encode($table_extra_columns);
            
            $data_table = $this->model->find($id);
            $table_name = config('database.prefix') . $data_table->table_name_en;
            $is_table = Db::query('SHOW TABLES LIKE '."'".$table_name."'");
            // var_dump('SHOW TABLES LIKE '."'".$table_name."';");

            $this->assign('table_extra_columns', $table_extra_columns);
            $this->assign('data_table', $data_table);
            $this->assign('is_table', $is_table);
            $this->assign('table_id', $id);
            return $this->fetch();
        }
    }

    /**
     * 删除并重新生成数据表 dropAndCreateTable
     */
    function dropAndCreateTable($id = 0)
    {
        if (Request::instance()->isAjax()) {
            $table_extra_column_model = new TableExtraColumnModel();

            $data_table = $this->model->find($id);
            $table_name_en=$data_table->table_name_en;
            $table_name_cn=$data_table->table_name_cn;

            // 删除数据表
            $str_create_table ='DROP TABLE IF EXISTS `' . $table_name_en . '`;'. "\n";
            
            // 系统字段
            $str_create_table .= 'CREATE TABLE `' . $table_name_en . '`  ('. "\n";
            $str_create_table .= '`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,'. "\n";
            $str_create_table .= "`category_id` smallint(5) UNSIGNED NOT NULL DEFAULT 0 COMMENT '栏目id',". "\n";
            $str_create_table .= "`title` char(100)  NOT NULL DEFAULT '' COMMENT '标题',";
            $str_create_table .= "`userid`  smallint(10) UNSIGNED NOT NULL DEFAULT 0  COMMENT '发布者id',". "\n";
            $str_create_table .= "`username` char(20) NOT NULL DEFAULT ''  COMMENT '发布人',". "\n";
            $str_create_table .= "`create_time` int(10) UNSIGNED NOT NULL DEFAULT 0  COMMENT '发布时间',". "\n";
            
            // extra 字段
            $table_extra_columns = $table_extra_column_model->where('table_id', $id)
                ->field('column_name_en,column_name_cn,type_length, note')
                ->select();
            foreach ($table_extra_columns as $row) {
                if ($row['note']) {
                    $str_create_table .= "`".$row['column_name_en']."` ".$row['type_length']." NOT NULL DEFAULT '' COMMENT '".$row['column_name_cn']." ".$row['note']."',". "\n";
                } else {
                    $str_create_table .= "`".$row['column_name_en']."` ".$row['type_length']." NOT NULL DEFAULT '' COMMENT '".$row['column_name_cn']."',". "\n";
                }
            }
            $str_create_table .= 'PRIMARY KEY (`id`)'."\n";
            $str_create_table .= ") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '".$table_name_cn."';";

            $database = config("database");

            // 连接数据并执行 multi_query
            $mysqli = new \mysqli($database['hostname'],  $database['username'], $database['password'], $database['database']);

            if ($mysqli->connect_error) {
                die('Connect Error (' . $mysqli->connect_errno . ') '
                    . $mysqli->connect_error);
            }
            $mysqli->set_charset("utf8");
            // echo 'Success... ' . $mysqli->host_info . "\n";
            $res = $mysqli->multi_query($str_create_table);

            $mysqli->close();
            if ($res) {
                $this->success('操作成功');
            }
            $this->error('操作失败, 请稍后尝试');
        }
    }
}